import akshare as ak
import pymysql
import pandas as pd
import stock_name2id

class historyDate:
    def __init__(self):
        self.sql_connect()

    def sql_connect(self):
        self.stock_db = pymysql.connect(host='127.0.0.1',  # 连接名称，默认127.0.0.1
                                        user='root',  # 用户名
                                        passwd='s159632487',  # 密码
                                        port=3306,  # 端口，默认为3306
                                        db='chujianquant',  # 数据库名称
                                        charset='utf8')  # 字符编码)
    def sse_summary(self):#上海交易所的
        stock_sse_summary = ak.stock_sse_summary()
        cursor = self.stock_db.cursor()
        state = 1
        try:
            delete = "DELETE FROM summary_shanghai"
            cursor.execute(delete)
            self.stock_db.commit()
        except:
            print("the summary_shanghai has not established ")

        sql = "CREATE TABLE `chujianquant`.`summary_shanghai` ( \
                      `type` VARCHAR(45) NOT NULL, \
                      `stock` VARCHAR(45) NULL, \
                      `science_and_technology_innovation_board` VARCHAR(45) NULL, \
                      `main` VARCHAR(45) NULL, \
                      PRIMARY KEY (`type`));"
        cursor.execute(sql)
        self.stock_db.commit()
        for i in range(0,len(stock_sse_summary)+1):
            if i!=7:
                type = str(stock_sse_summary['项目'][i])
                stock = str(stock_sse_summary['股票'][i])
                science_and_technology_innovation_board = str(stock_sse_summary['科创板'][i])
                main = str(stock_sse_summary['主板'][i])
                sql = "INSERT INTO summary_shanghai (type,stock,science_and_technology_innovation_board,main) VALUES ('%s', '%s', '%s', '%s')" % (type, stock,science_and_technology_innovation_board,main)
                try:
                    cursor.execute(sql)
                    self.stock_db.commit()
                except:
                    state = 0
                    print("insert summary_shanghai" + str(i) + "data failed")
        if state == 1:
            print("load in summary_shanghai successfully")
        cursor.close()
        self.stock_db.close()

    def szse_summary(self):
        stock_szse_summary_df = ak.stock_szse_summary(date="20220415")#选择最近的交易日
        cursor = self.stock_db.cursor()
        try:
            delete = "DELETE FROM summary_shenzhen"
            cursor.execute(delete)
            self.stock_db.commit()
        except:
            print("the summary_shenzhen has not established ")
        state = 1
        sql = "CREATE TABLE `chujianquant`.`summary_shenzhen` ( \
                              `type` VARCHAR(45) NOT NULL, \
                              `quantity` VARCHAR(45) NULL, \
                              `monetary_deal` VARCHAR(45) NULL, \
                              `quantity_deal` VARCHAR(45) NULL, \
                              `total_stock` VARCHAR(45) NULL, \
                              `total_value` VARCHAR(45) NULL, \
                              `current_stock` VARCHAR(45) NULL, \
                              `current_value` VARCHAR(45) NULL, \
                              PRIMARY KEY (`type`));"
        cursor.execute(sql)
        self.stock_db.commit()
        for i in range(0, len(stock_szse_summary_df)):
                type = str(stock_szse_summary_df['证券类别'][i])
                quantity = str(stock_szse_summary_df['数量'][i])
                monetary_deal = str(stock_szse_summary_df['成交金额'][i])
                quantity_deal = str(stock_szse_summary_df['成交量'][i])
                total_stock = str(stock_szse_summary_df['总股本'][i])
                total_value = str(stock_szse_summary_df['总市值'][i])
                current_stock = str(stock_szse_summary_df['流通股本'][i])
                current_value = str(stock_szse_summary_df['流通市值'][i])
                sql = "INSERT INTO summary_shenzhen (type, quantity, monetary_deal, quantity_deal, total_stock, \
                 total_value, current_stock, current_value) VALUES ('%s', '%s', '%s', '%s','%s','%s','%s','%s')" % \
                (type, quantity, monetary_deal, quantity_deal, total_stock, total_value, current_stock, current_value)
                try:
                    cursor.execute(sql)
                    self.stock_db.commit()
                except:
                    state = 0
                    print("insert summary_shenzhen"+str(i)+"data failed")
        if state == 1:
            print("load in summary_shenzhen successfully")
        cursor.close()
        self.stock_db.close()

    def get_history_data_daily(self,name,begin,end):
        """
        :param name: 指定公司查找历史数据
        步骤：现将现有的表删除，然后创建表，向表里插入数据
        :return:写入到数据库中
        """
        stock_zh_a_hist_df = ak.stock_zh_a_hist(symbol=name, period="daily", start_date=begin,end_date=end, adjust="") #不复权

        namestr = stock_name2id.stockName2Id().search_name(name)
        namestr = namestr[0][0].split()
        namestr = "".join(namestr)+"daily"
        state = 1
        try:
            cursor = self.stock_db.cursor()
            sql1 = "DROP TABLE `chujianquant`.`%s`" % (namestr)
            cursor.execute(sql1)
        except:
            print("table "+namestr+" has not established")
        cursor = self.stock_db.cursor()
        sql="CREATE TABLE `chujianquant`.`%s` ( \
              `date` VARCHAR(45) NOT NULL, \
              `open` VARCHAR(45) NULL, \
              `close` VARCHAR(45) NULL, \
              `high` VARCHAR(45) NULL, \
              `low` VARCHAR(45) NULL, \
              `quantity_deal` VARCHAR(45) NULL, \
              `value_deal` VARCHAR(45) NULL, \
              `swing` VARCHAR(45) NULL, \
              `percentage_change` VARCHAR(45) NULL, \
              `value_change` VARCHAR(45) NULL, \
              `turnover_rate` VARCHAR(45) NULL, \
              PRIMARY KEY (`date`));"%(namestr)
        cursor.execute(sql)
        self.stock_db.commit()
        cursor.close()

        cursor = self.stock_db.cursor()
        for i in range(0, len(stock_zh_a_hist_df)):
            date = str(stock_zh_a_hist_df['日期'][i])
            open = str(stock_zh_a_hist_df['开盘'][i])
            close = str(stock_zh_a_hist_df['收盘'][i])
            high = str(stock_zh_a_hist_df['最高'][i])
            low = str(stock_zh_a_hist_df['最低'][i])
            quantity_deal = str(stock_zh_a_hist_df['成交量'][i])
            value_deal = str(stock_zh_a_hist_df['成交额'][i])
            swing = str(stock_zh_a_hist_df['振幅'][i])
            percentage_change = str(stock_zh_a_hist_df['涨跌幅'][i])
            value_change = str(stock_zh_a_hist_df['涨跌额'][i])
            turnover_rate = str(stock_zh_a_hist_df['换手率'][i])
            sql = "INSERT INTO `chujianquant`.`%s`(date, open, close, high, low, quantity_deal, value_deal, swing, percentage_change,value_change, turnover_rate) VALUES ('%s','%s','%s','%s', '%s', '%s', '%s','%s','%s','%s','%s')"  \
                  % (namestr, date, open, close, high, low, quantity_deal, value_deal, swing, percentage_change,value_change, turnover_rate)
            try:

                cursor.execute(sql)
                self.stock_db.commit()

            except:
                state = 0
                print("insert"+" "+str(i)+" "+"history daily data failed")
        if state==1:
            print("load in " + namestr+ " history daily data  sucessfully")

        cursor.close()
        self.stock_db.close()
        #stock_zh_a_hist_df = ak.stock_zh_a_hist(symbol="000001", period="daily", start_date="20170301",end_date='20210907', adjust="qfq")  前复权
        #stock_zh_a_hist_df = ak.stock_zh_a_hist(symbol="000001", period="daily", start_date="20170301",end_date='20210907', adjust="hfq")  后复权



    def get_history_data_minute(self,name,minute,begin,end):
        # stock_zh_a_hist_min_em_df = ak.stock_zh_a_hist_min_em(symbol="000001", start_date="2021-09-01 09:32:00",
        #                                                       end_date="2021-09-06 09:32:00", period='5', adjust='hfq')
        stock_zh_a_hist_min_em_df = ak.stock_zh_a_hist_min_em(symbol=name, start_date=begin,
                                                              end_date=end, period=minute, adjust='')#同理，adjust来判断是不是复权


        namestr = stock_name2id.stockName2Id().search_name(name)
        namestr = namestr[0][0].split()
        namestr = "".join(namestr)+minute+"minute"
        state = 1

        try:
            cursor = self.stock_db.cursor()
            sql1 = "DROP TABLE `chujianquant`.`%s`" % (namestr)
            cursor.execute(sql1)
        except:
            print("table "+namestr+" has not established")
        cursor = self.stock_db.cursor()
        sql = "CREATE TABLE `chujianquant`.`%s` ( \
              `date` VARCHAR(45) NOT NULL, \
              `open` VARCHAR(45) NULL, \
              `close` VARCHAR(45) NULL, \
              `high` VARCHAR(45) NULL, \
              `low` VARCHAR(45) NULL, \
              `quantity_deal` VARCHAR(45) NULL, \
              `value_deal` VARCHAR(45) NULL, \
              `latest_price` VARCHAR(45) NULL, \
              PRIMARY KEY (`date`)); " % (namestr)
        cursor.execute(sql)
        self.stock_db.commit()
        cursor.close()

        cursor = self.stock_db.cursor()
        for i in range(0, len(stock_zh_a_hist_min_em_df)):
            date = str(stock_zh_a_hist_min_em_df['时间'][i])
            open = str(stock_zh_a_hist_min_em_df['开盘'][i])
            close = str(stock_zh_a_hist_min_em_df['收盘'][i])
            high = str(stock_zh_a_hist_min_em_df['最高'][i])
            low = str(stock_zh_a_hist_min_em_df['最低'][i])
            quantity_deal = str(stock_zh_a_hist_min_em_df['成交量'][i])
            value_deal = str(stock_zh_a_hist_min_em_df['成交额'][i])
            latest_price = str(stock_zh_a_hist_min_em_df['最新价'][i])

            sql = "INSERT INTO `chujianquant`.`%s`(date, open, close, high, low, quantity_deal, value_deal, latest_price) VALUES ('%s','%s','%s','%s', '%s', '%s', '%s','%s')" \
                  % (namestr, date, open, close, high, low, quantity_deal, value_deal, latest_price)
            try:

                cursor.execute(sql)
                self.stock_db.commit()

            except:
                state = 0
                print("insert" + " " + str(i) + " " + "history minute data failed")
        if state == 1:
            print("load in " + namestr+ " history minute data  sucessfully")

        cursor.close()
        self.stock_db.close()


    def get_prehistory_data(self,name):#距离当前最近的交易日的行情数据
        stock_zh_a_hist_pre_min_em_df = ak.stock_zh_a_hist_pre_min_em(symbol=name)


        namestr = stock_name2id.stockName2Id().search_name(name)
        namestr = namestr[0][0].split()
        namestr = "".join(namestr) + "latest"
        state = 1

        try:
            cursor = self.stock_db.cursor()
            sql1 = "DROP TABLE `chujianquant`.`%s`" % (namestr)
            cursor.execute(sql1)
        except:
            print("table "+namestr+" has not established")
        cursor = self.stock_db.cursor()
        sql = "CREATE TABLE `chujianquant`.`%s` ( \
              `date` VARCHAR(45) NOT NULL, \
              `open` VARCHAR(45) NULL, \
              `close` VARCHAR(45) NULL, \
              `high` VARCHAR(45) NULL, \
              `low` VARCHAR(45) NULL, \
              `quantity_deal` VARCHAR(45) NULL, \
              `value_deal` VARCHAR(45) NULL, \
              `latest_price` VARCHAR(45) NULL, \
              PRIMARY KEY (`date`)); " % (namestr)
        cursor.execute(sql)
        self.stock_db.commit()
        cursor.close()

        cursor = self.stock_db.cursor()
        for i in range(0, len(stock_zh_a_hist_pre_min_em_df)):
            date = str(stock_zh_a_hist_pre_min_em_df['时间'][i])
            open = str(stock_zh_a_hist_pre_min_em_df['开盘'][i])
            close = str(stock_zh_a_hist_pre_min_em_df['收盘'][i])
            high = str(stock_zh_a_hist_pre_min_em_df['最高'][i])
            low = str(stock_zh_a_hist_pre_min_em_df['最低'][i])
            quantity_deal = str(stock_zh_a_hist_pre_min_em_df['成交量'][i])
            value_deal = str(stock_zh_a_hist_pre_min_em_df['成交额'][i])
            latest_price = str(stock_zh_a_hist_pre_min_em_df['最新价'][i])

            sql = "INSERT INTO `chujianquant`.`%s`(date, open, close, high, low, quantity_deal, value_deal, latest_price) VALUES ('%s','%s','%s','%s', '%s', '%s', '%s','%s')" \
                  % (namestr, date, open, close, high, low, quantity_deal, value_deal, latest_price)
            try:

                cursor.execute(sql)
                self.stock_db.commit()

            except:
                state = 0
                print("insert " + " " + str(i) + " " + " history latest data failed")
        if state == 1:
            print("load in " + namestr+ " latest data  sucessfully")

        cursor.close()
        self.stock_db.close()


    def get_history_order(self,name,day):#指定日期的历史成交数据
        stock_zh_a_tick_tx_df = ak.stock_zh_a_tick_tx(symbol=name, trade_date=day)

        namestr = stock_name2id.stockName2Id().search_name(name[2:])
        namestr = namestr[0][0].split()
        namestr = "".join(namestr) + day + "order"
        # pd.set_option('display.max_rows', None)
        # print(stock_zh_a_tick_tx_df)
        # print(namestr)
        state = 1
        try:
            cursor = self.stock_db.cursor()
            sql1 = "DROP TABLE `chujianquant`.`%s`" % (namestr)
            cursor.execute(sql1)
        except:
            print("table "+namestr+" has not established")
        cursor = self.stock_db.cursor()
        sql = "CREATE TABLE `chujianquant`.`%s` ( \
              `date_deal` VARCHAR(45) NOT NULL, \
              `price_deal` VARCHAR(45) NULL, \
              `value_change` VARCHAR(45) NULL, \
              `quantity_deal_hands` VARCHAR(45) NULL, \
              `value_deal_yuan` VARCHAR(45) NULL, \
              `type` VARCHAR(45) NULL, \
              PRIMARY KEY (`date_deal`)); " % (namestr)
        cursor.execute(sql)
        self.stock_db.commit()
        cursor.close()

        cursor = self.stock_db.cursor()
        for i in range(0, len(stock_zh_a_tick_tx_df)):
            date_deal = str(stock_zh_a_tick_tx_df['成交时间'][i])
            price_deal = str(stock_zh_a_tick_tx_df['成交价格'][i])
            value_change = str(stock_zh_a_tick_tx_df['价格变动'][i])
            quantity_deal_hands = str(stock_zh_a_tick_tx_df['成交量(手)'][i])
            value_deal_yuan = str(stock_zh_a_tick_tx_df['成交额(元)'][i])
            type = str(stock_zh_a_tick_tx_df['性质'][i])

            sql = "INSERT INTO `chujianquant`.`%s`(date_deal, price_deal, value_change, quantity_deal_hands, value_deal_yuan, type) VALUES ('%s', '%s', '%s','%s', '%s', '%s')" \
                  % (namestr, date_deal, price_deal, value_change, quantity_deal_hands, value_deal_yuan, type)
            try:

                cursor.execute(sql)
                self.stock_db.commit()

            except:
                state = 0
                print("insert " + " " + str(i) + " " + "history order data failed")
                print(stock_zh_a_tick_tx_df['成交时间'][i])
        if state == 1:
            print("load in " + namestr+ " order data  sucessfully")

        cursor.close()
        self.stock_db.close()

    def get_prehistory_order(self,name):#最新的成交数据
        stock_zh_a_tick_tx_js_df = ak.stock_zh_a_tick_tx_js(symbol=name)
        namestr = stock_name2id.stockName2Id().search_name(name[2:])
        namestr = namestr[0][0].split()
        namestr = "".join(namestr) + 'latest_order'
        # print(stock_zh_a_tick_tx_js_df)
        # print(namestr)
        state = 1
        try:
            cursor = self.stock_db.cursor()
            sql1 = "DROP TABLE `chujianquant`.`%s`" % (namestr)
            cursor.execute(sql1)
        except:
            print("table "+namestr+" has not established")
        cursor = self.stock_db.cursor()
        sql = "CREATE TABLE `chujianquant`.`%s` ( \
              `date_deal` VARCHAR(45) NOT NULL, \
              `price_deal` VARCHAR(45) NULL, \
              `value_change` VARCHAR(45) NULL, \
              `quantity_deal_hands` VARCHAR(45) NULL, \
              `value_deal_yuan` VARCHAR(45) NULL, \
              `type` VARCHAR(45) NULL, \
              PRIMARY KEY (`date_deal`)); " % (namestr)
        cursor.execute(sql)
        self.stock_db.commit()
        cursor.close()

        cursor = self.stock_db.cursor()
        for i in range(0, len(stock_zh_a_tick_tx_js_df)):
            date_deal = str(stock_zh_a_tick_tx_js_df['成交时间'][i])
            price_deal = str(stock_zh_a_tick_tx_js_df['成交价格'][i])
            value_change = str(stock_zh_a_tick_tx_js_df['价格变动'][i])
            quantity_deal_hands = str(stock_zh_a_tick_tx_js_df['成交量'][i])
            value_deal_yuan = str(stock_zh_a_tick_tx_js_df['成交金额'][i])
            type = str(stock_zh_a_tick_tx_js_df['性质'][i])

            sql = "INSERT INTO `chujianquant`.`%s`(date_deal, price_deal, value_change, quantity_deal_hands, value_deal_yuan, type) VALUES ('%s', '%s', '%s','%s', '%s', '%s')" \
                  % (namestr, date_deal, price_deal, value_change, quantity_deal_hands, value_deal_yuan, type)
            try:

                cursor.execute(sql)
                self.stock_db.commit()

            except:
                state = 0
                print("insert history" + " " + str(i) + " " + "order data failed")
                print(stock_zh_a_tick_tx_js_df['成交时间'][i])
        if state == 1:
            print("load in " + namestr+ " order data  sucessfully")

        cursor.close()
        self.stock_db.close()


if __name__  == "__main__":
    historyDate().sse_summary()
    historyDate().szse_summary()
    historyDate().get_history_data_daily('000001',"20210426","20220426")
    historyDate().get_history_data_minute("000001","1","2022-04-25 10:00:00","2022-04-26 10:00:00")
    historyDate().get_prehistory_data("000002")
    historyDate().get_history_order("sh600848","20191011")
    historyDate().get_prehistory_order("sz000001")